import sqlite3
from datetime import date

# 连接数据库
conn = sqlite3.connect('backend/questions.db')
cursor = conn.cursor()

print("=== 修复数据库中的错误数据 ===")

# 查看当前错误的数据
print("\n修复前的数据:")
cursor.execute("SELECT id, student_name, score, submission_date, class_id, submission_time, client_ip FROM submissions")
submissions = cursor.fetchall()
for submission in submissions:
    print(f"ID: {submission[0]}, 学生: {submission[1]}, 得分: {submission[2]}, 日期: {submission[3]}, 班级: {submission[4]}, 时间: {submission[5]}, IP: {submission[6]}")

# 修复数据：交换score和client_ip字段的值
print("\n开始修复数据...")
for submission in submissions:
    submission_id = submission[0]
    current_score = submission[2]  # 这里存储的是IP地址
    current_ip = submission[6]     # 这里存储的是分数
    
    # 只修复那些score字段包含IP地址格式的记录
    if isinstance(current_score, str) and ('.' in current_score or current_score == 'None'):
        # 交换数据
        new_score = current_ip if current_ip is not None else 0
        new_ip = current_score if current_score != 'None' else None
        
        cursor.execute(
            "UPDATE submissions SET score = ?, client_ip = ? WHERE id = ?",
            (new_score, new_ip, submission_id)
        )
        print(f"修复记录 {submission_id}: 得分 {current_score} -> {new_score}, IP {current_ip} -> {new_ip}")

# 提交更改
conn.commit()

# 查看修复后的数据
print("\n修复后的数据:")
cursor.execute("SELECT id, student_name, score, submission_date, class_id, submission_time, client_ip FROM submissions")
submissions = cursor.fetchall()
for submission in submissions:
    print(f"ID: {submission[0]}, 学生: {submission[1]}, 得分: {submission[2]}, 日期: {submission[3]}, 班级: {submission[4]}, 时间: {submission[5]}, IP: {submission[6]}")

# 关闭连接
conn.close()
print("\n数据库修复完成！")